package com.atlyn.dao;

import com.atlyn.bean.Classroom;
import com.atlyn.bean.Plan;
import com.atlyn.bean.Teacher;
import com.atlyn.bean.TeacherClassroomTime;
import com.atlyn.utils.Tool;
import org.junit.Test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class FuncDao {
    Tool tool = new Tool();
    //查询所有教室信息
    public List<Classroom> func1(){
        String sql = "select* from classroom";
        List<Classroom> classrooms = tool.getForList(Classroom.class,sql);
        return classrooms;
    }
    @Test
    public void play1(){
        List<Classroom> list = func1();
        for(int i = 0;i < list.size();i++){
            System.out.println(list.get(i));
        }
    }
    //根据教室号查询教室信息
    public Classroom search(String clnum){
        String sql = "select* from classroom where clnum=?";
        Classroom classroom = tool.getInstance(Classroom.class,sql,clnum);
        return classroom;
    }
    @Test
    public void play2(){
        System.out.println(search("102"));
    }
    //添加新教室
    public boolean addClassroom(String clnum,String type,String medium,int capacity){
        String sql = "insert into classroom values(?,?,?,?)";
        int count = tool.update(sql,clnum,type,medium,capacity);
        if (count > 0){
            return true;
        }else {
            return false;
        }
    }
    //根据教室号删除教室信息
    public void deleteClassroom(String clnum){
        String sql = "delete from classroom where clnum=?";
        tool.update(sql,clnum);
    }
    //修改教室信息
    public void editClassroom(String clnum,String type,String medium,int capacity){
        String sql = "update classroom set type=?,medium=?,capacity=? where clnum=?";
        tool.update(sql,type,medium,capacity,clnum);

    }
    //根据教师id查询教师信息
    public Teacher SelectTeacher(int tid){
        String sql = "select* from teacher where tid=?";
        Teacher teacher = tool.getInstance(Teacher.class,sql,tid);
        return teacher;
    }
    //修改教师信息
    public void editTeacher(int tid,String tname,String course,String level,String tpwd){
        String sql = "update teacher set tname=?,course=?,level=?,tpwd=? where tid=?";
        tool.update(sql,tname,course,level,tpwd,tid);
    }
    //三表查询
    public List<TeacherClassroomTime> SelectCourse(int tid){
        String sql = "select tname,course,plan.clnum,type,medium,start,end from teacher,classroom,time,plan where plan.tid=? and teacher.tid=plan.tid and classroom.clnum=plan.clnum and time.timeid=plan.timeid group by planum";
        List<TeacherClassroomTime> teacherClassroomTimes = tool.getForList(TeacherClassroomTime.class,sql,tid);
        return teacherClassroomTimes;
    }
    @Test
    public void play3(){
         List<TeacherClassroomTime> teacherClassroomTimes = SelectCourse(1);
         for (int i=0;i<teacherClassroomTimes.size();i++){
             System.out.println(teacherClassroomTimes.get(i).toString());
         }
    }
    //查询所有课时
    public List<TeacherClassroomTime> SelectAllPlan(){
        String sql = "select tname,course,plan.clnum,type,medium,start,end from teacher,classroom,time,plan where teacher.tid=plan.tid and classroom.clnum=plan.clnum and time.timeid=plan.timeid group by planum";
        List<TeacherClassroomTime> teacherClassroomTimes = tool.getForList(TeacherClassroomTime.class,sql);
        return teacherClassroomTimes;

    }
    //将字符串转成Date类型
    public Date StringtoDate(String time){
        //字符串转Date类型
        Date date;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        time = time.replace("T"," ");
        try {
            date = format.parse(time);
            System.out.println("转换以后的时间：："+date);//Sun Feb 02 02:02:02 CST 2020
            return date;
        } catch (ParseException e) {
            e.printStackTrace();
            date =  null;
        }
        return date;
    }
    //查询time的数量
    public int PlanCount(){
        String sql = "select* from plan";
        List<Plan> plans = tool.getForList(Plan.class,sql);
        return plans.size();
    }
    //向plan和time插入数据
    public boolean addPlan(String planum,String clnum,int tid,String timeid,Date start,Date end){
        String sql1 = "insert into plan values(?,?,?,?)";
        String sql2 = "insert into time values(?,?,?)";
        int count2 = tool.update(sql2,timeid,start,end);
        int count1 = tool.update(sql1,planum,clnum,tid,timeid);

        if (count1 > 0 && count2 > 0){
            return true;
        }else {
            return false;
        }
    }
    //根据教师姓名查询教师信息
    public List<Teacher> selectTeahcerByName(String tname){
        String sql = "select* from teacher where tname=?";
        List<Teacher> teachers = tool.getForList(Teacher.class,sql,tname);
        return teachers;
    }
    //查询所有的教师信息
    public List<Teacher> selectAllTeacher(){
        String sql = "select* from teacher";
        List<Teacher> teachers = tool.getForList(Teacher.class,sql);
        return teachers;
    }

}

